package cn.sfa.service.news;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.rowset.serial.SerialException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import cn.sfa.common.pagination.Pagination;
import cn.sfa.common.utils.CommonUtil;
import cn.sfa.common.utils.MajorKeyGenerate;
import cn.sfa.common.utils.TableNameEnum;
import cn.sfa.entity.news.MonthBusiness;
import cn.sfa.entity.news.SaleNews;
import cn.sfa.entity.news.SaleNewsBranch;
import cn.sfa.entity.news.SaleNewsSee;
import cn.sfa.entity.users.SaleUsersLogin;

@Service
@Transactional
public class PreNewsService {

	Logger log = LoggerFactory.getLogger(PreNewsService.class);

	@Autowired
	NamedParameterJdbcTemplate jdbcTemplate;

	@Autowired
	private DefaultLobHandler lobHandler;
	@Autowired
	MajorKeyGenerate majorKeyGenerate;
	
	@Autowired
	NamedParameterJdbcTemplate jdbcTemplateName;

	/**
	 * 分页查询
	 */
	public Pagination searchNews(
			int currentPage,// 页码
			int numPerPage,// 页尺寸
			String url,// 分页url
			SaleNews saleNews, String beginCreateDate, String endCreateDate,
			SaleUsersLogin user) {

		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append("select * from salenews s where 1=1 ");
		
		sqlStr.append("and (:newstitle is null or s.newstitle like :newstitle) ");
		sqlStr.append("and (:newscontent is null or s.newscontent like :newscontent) ");
		sqlStr.append("and (:begincreatedate is null or s.CREATEDATE >= date_format(:begincreatedate, '%Y-%m-%d')) ");
        sqlStr.append("and (:endcreatedate is null or s.CREATEDATE <= date_format(concat(:endcreatedate,' 23:59:59'),'%Y/%m/%d %T')) ");
		Map<String,Object> paramMap = new HashMap<String,Object>(); 
		paramMap.put("newstitle", CommonUtil.isEmpty(saleNews.getNewsTitle())?null:"%"+saleNews.getNewsTitle()+"%");
		paramMap.put("newscontent", CommonUtil.isEmpty(saleNews.getNewsContent())?null:"%"+saleNews.getNewsContent()+"%");
		paramMap.put("begincreatedate", CommonUtil.isEmpty(beginCreateDate)?null:beginCreateDate);
		paramMap.put("endcreatedate", CommonUtil.isEmpty(endCreateDate)?null:endCreateDate);
		 
		return new Pagination(sqlStr.toString(), paramMap, currentPage,
				numPerPage, url, jdbcTemplate);
	}

	public boolean deleteNewsById(String saleNewsId) {
		String sql = "delete salenews where salenewsid=:saleNewsId";
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("saleNewsId", saleNewsId);
		int count = jdbcTemplate.update(sql, map);
		if (count > 0) {
			sql = "delete salenewsbranch where salenewsid=:saleNewsId";
			jdbcTemplate.update(sql, map);
			return true;
		}
		return false;
	}

	public SaleNews getNew(String saleNewsId) {
		String sql = "select * from salenews where SALENEWSID=:SALENEWSID ";
		final SaleNews news = new SaleNews();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("SALENEWSID", saleNewsId);
		jdbcTemplate.query(sql, paramMap, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				news.setSaleNewsId(rs.getString("salenewsid"));
				news.setCreateDate(rs.getDate("createDate"));
				news.setCreateUid(rs.getString("createuser"));
				news.setUpdateDate(rs.getDate("updatedate"));
				news.setUpdateId(rs.getString("updateuser"));
				news.setNewsTitle(rs.getString("newstitle"));
				String clobText = lobHandler.getClobAsString(rs, "NEWSCONTENT");
				news.setNewsContent(clobText);
			}

		});

		return news;
	}

	/**
	 * 编辑新闻
	 * 
	 * @param saleNews
	 * @return boolean
	 * @throws SQLException
	 * @throws SerialException
	 */
	public boolean updateNews(final SaleNews saleNews) {
		String sql = "update salenews set "
				+ "SALENEWSID=?,UPDATEDATE=?,UPDATEUSER=?,"
				+ "HOTKEY=?,AGENTLEVEL=?,NEWTYPE=?,ISAFFIX=?,AFFIXPAT=?,"
				+ "ISSHARE=?,VIEWSEQ=?,NEWSTITLE=?,INSENDTO=?,NEWSCONTENT=?,"
				+ "SENDTOTYPE=? where salenewsid=?";

		int count = jdbcTemplate.getJdbcOperations().execute(
				sql,
				new AbstractLobCreatingPreparedStatementCallback(
						this.lobHandler) {
					protected void setValues(PreparedStatement ps,
							LobCreator lobCreator) throws SQLException,
							DataAccessException {
						ps.setString(1, saleNews.getSaleNewsId());
						ps.setDate(2, new Date(saleNews.getUpdateDate()
								.getTime()));
						ps.setString(3, CommonUtil.jugeAndFixNull(saleNews
								.getUpdateId()));
						ps.setString(4, CommonUtil.jugeAndFixNull(saleNews
								.getHotKey()));
						ps.setString(5, CommonUtil.jugeAndFixNull(saleNews
								.getAgentLevel()));
						ps.setString(6, CommonUtil.jugeAndFixNull(saleNews
								.getNewType()));
						ps.setString(7, CommonUtil.jugeAndFixNull(saleNews
								.getIsAffix()));
						ps.setString(8, CommonUtil.jugeAndFixNull(saleNews
								.getAffixPat()));
						ps.setString(9, CommonUtil.jugeAndFixNull(saleNews
								.getIsShare()));
						ps.setInt(10, saleNews.getViewSeq());
						ps.setString(11, CommonUtil.jugeAndFixNull(saleNews
								.getNewsTitle()));
						ps.setString(12, CommonUtil.jugeAndFixNull(saleNews
								.getInSendto()));

						lobCreator.setClobAsString(ps, 13, saleNews
								.getNewsContent()); // 设置详细的CLOB字段的内容

						ps.setString(14, CommonUtil.jugeAndFixNull(saleNews
								.getSendToType()));
						ps.setString(15, CommonUtil.jugeAndFixNull(saleNews
								.getSaleNewsId()));

					}
				});

		if (count > 0) {
			return true;
		}
		return false;
	}

	/**
	 * 通过用户所属机构取得可读新闻
	 * 
	 * @param saleNewsBranchId
	 * @return
	 */
	public Pagination getNewsByBranch(int currentPage,// 页码
			int numPerPage,// 页尺寸
			String url,// 分页url
			String saleNewsBranchId,String agentid ,String type,String userType,String beginCreateDate,String endCreateDate,SaleNews saleNews) {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		
		
		String sql="select sns.saleNewsId,sns.newstitle,sns.newscontent,sns.newtype,sns.createdate,sns.affixpat,sns.NEWAFFIXURL1 from salenews sns ";
		sql+=" where sns.salenewsid in(select snb.salenewsid from salenewsbranch snb where ((:usertype='USERTYPE_3') or  (snb.branchid=:saleNewsBranchId or snb.branchid='BRA0000000000001')))";
		if("USERTYPE_1".equals(userType) || "USERTYP_2".equals(userType))
		{
			sql = "select sns.saleNewsId,sns.newstitle,sns.newscontent,sns.newtype,sns.createdate,se.syscode,sss.agentlevelcode,sns.affixpat,sns.NEWAFFIXURL1 from salenews sns,salesyscode se,(select distinct dd.agentlevelcode from agentbroker dd ,salesyscode ss where dd.agentid=:agentid and dd.agentlevelcode=ss.syscode) sss where sns.salenewsid "
				+ "in(select snb.salenewsid from salenewsbranch snb where snb.branchid=:saleNewsBranchId or snb.branchid='BRA0000000000001')  and (sns.agentlevel=se.salerecordid or sns.agentlevel='ALL') ";
		sql+=" and se.syscode=sss.agentlevelcode ";
			paramMap.put("agentid", agentid);
		}

		if (CommonUtil.isEmpty(type) || "1".equals(type)) {
			sql += "and (newtype='NEWSTYPE_01' or newtype='NEWSTYPE_02')";
		} else if ("2".equals(type)) {
			sql += "and newtype='NEWSTYPE_03' ";
		} else if ("3".equals(type)) {
			sql += "and newtype='NEWSTYPE_04' ";
		} else {
			sql += "and (newtype='NEWSTYPE_01' or newtype='NEWSTYPE_02')";
		}
		if(!CommonUtil.isEmpty(saleNews.getNewsTitle())){
			
			sql += " and sns.newstitle like :NEWSTITLE ";
			paramMap.put("NEWSTITLE", "%"+CommonUtil.jugeAndFixNull(saleNews.getNewsTitle())+"%");
		}
		if(!CommonUtil.isEmpty(saleNews.getNewType()) && !"ALL".equals(saleNews.getNewType())){
			
			sql += " and sns.newtype = :NEWTYPE ";
			paramMap.put("NEWTYPE", CommonUtil.jugeAndFixNull(saleNews.getNewType()));
		}
		if(!CommonUtil.isEmpty(beginCreateDate)){
			
			sql += " and sns.CREATEDATE >= to_date(:BEGINCREATEDATE,'yyyy/MM/dd')";
			paramMap.put("BEGINCREATEDATE", beginCreateDate);
		}
		if(!CommonUtil.isEmpty(endCreateDate)){
			
			sql += " and sns.CREATEDATE <= to_date(:ENDCREATEDATE || ' 23:59:59','yyyy/MM/dd hh24:mi:ss')"; 
			paramMap.put("ENDCREATEDATE", endCreateDate);
		}
		sql += " order by sns.createdate desc";
		
		paramMap.put("saleNewsBranchId", saleNewsBranchId);
		paramMap.put("usertype", userType);
		return new Pagination(sql, paramMap, currentPage, numPerPage, url,
				jdbcTemplate);
	}
	
	/**
	 * 通过用户ID取得本月所有业务数
	 * @param agentID
	 * @return
	 */
	public MonthBusiness getThisMonthBusinessAll(String agentID){
		Map<String, Object> paramMap = new HashMap<String, Object>();
		String sqlStr="select * from table(PKG_SFA_THISMONTHBUSINESSALL.getThisMonthBusinessAll(:I_AGENTID))";
		paramMap.put("I_AGENTID", CommonUtil.jugeAndFixNull(agentID));
		List<MonthBusiness> mothbusiness = new ArrayList<MonthBusiness>();
		mothbusiness = jdbcTemplate.query(sqlStr, paramMap, new BeanPropertyRowMapper<MonthBusiness>(MonthBusiness.class));
		return mothbusiness.get(0);
	}
	/**
	 * 通过用户ID和类型取得本月所有业务基础数据
	 * @param agentID
	 * @param type
	 * @return
	 */
	public Pagination getThisMonthBusiness(
			int currentPage,// 页码
			int numPerPage,// 页尺寸
			String url,// 分页url
			String agentID,String type){
		Map<String, Object> paramMap = new HashMap<String, Object>();
		String sql="select * from table(pkg_sfa_thismonthbusiness.getTHISMONTHBUSINESS(:agentID, :type))";
		paramMap.put("agentID", CommonUtil.jugeAndFixNull(agentID));
		paramMap.put("type", CommonUtil.jugeAndFixNull(type));
		return new Pagination(sql, paramMap, currentPage, numPerPage, url,
				jdbcTemplate);
	}
	
	/**
	 * 统计访问率
	 * @param saleNews
	 * @return boolean
	 * @throws SQLException 
	 * @throws SerialException 
	 */
	public boolean newsCount(final SaleNewsSee saleNewsSee)
	{	
		String sql="insert into salenewssee values(:SALENEWSSEEID,sysdate,:CREATEUSER,sysdate," +
				":UPDATEUSER,:NEWSID,sysdate," +
				":VCOL1,:VCOL2,:VCOL3,:VCOL4,:VCOL5)";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("SALENEWSSEEID",majorKeyGenerate.keyGenerate(TableNameEnum.SALENEWSSEE));
		paramMap.put("CREATEUSER", saleNewsSee.getCreateUid());
		paramMap.put("UPDATEUSER", saleNewsSee.getCreateUid());
		paramMap.put("NEWSID", saleNewsSee.getNewsId());
		paramMap.put("VCOL1", CommonUtil.jugeAndFixNull(saleNewsSee.getVcol1()));
		paramMap.put("VCOL2", CommonUtil.jugeAndFixNull(saleNewsSee.getVcol2()));
		paramMap.put("VCOL3", CommonUtil.jugeAndFixNull(saleNewsSee.getVcol3()));
		paramMap.put("VCOL4", CommonUtil.jugeAndFixNull(saleNewsSee.getVcol4()));
		paramMap.put("VCOL5", CommonUtil.jugeAndFixNull(saleNewsSee.getVcol5()));
		
		int count=	jdbcTemplateName.update(sql, paramMap);
			
		if(count>0)
			return true;
		return false;
	}
}
